package com.core.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.core.dao.ContactDAO;
import com.core.database.Manager;
import com.core.entities.Contact;
import com.core.exception.DAOException;

public class ContactDAOImpl implements ContactDAO {
	Contact contact = new Contact();
	Connection connection = null;
	PreparedStatement ps = null;
	ResultSet rs = null;
	ArrayList<Contact> contactList = new ArrayList<Contact>();

	private static ContactDAOImpl instance = null;

	public static ContactDAOImpl getInstance() {
		if (instance == null) {
			instance = new ContactDAOImpl();
		}
		return instance;
	}

	@Override
	public void insert(Contact o) throws DAOException {
		try {
			connection = Manager.getConnection();
			String sql = "INSERT INTO contact (first_name, second_name, third_name, last_name, address, "
					+ "city_id, land_phone,mobile_number, email, website, job, work_id,"
					+ " online_account_id, username_id )"
					+ " VALUE (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?)";
			ps = connection.prepareStatement(sql);
			ps.setString(1, o.getFirst_name());
			ps.setString(2, o.getSecond_name());
			ps.setString(3, o.getThird_name());
			ps.setString(4, o.getLast_name());
			ps.setString(5, o.getAddress());
			ps.setInt(6, o.getCity_id());
			ps.setString(7, o.getLand_phone());
			ps.setString(8, o.getMobile_number());
			ps.setString(9, o.getEmail());
			ps.setString(10, o.getWebsite());
			ps.setString(11, o.getJob());
			ps.setInt(12, o.getWork_id());
			ps.setInt(13, o.getOnline_account_id());
			ps.setInt(14, o.getUsername_id());
			int result = ps.executeUpdate();
			if (result != 1) {
				throw new SQLException("No row inserted in Contacts table!");
			}
		} catch (Exception ex) {
			ex.printStackTrace();
			throw new RuntimeException("cannot add new Contacts", ex);
		} finally {
			Manager.closeConnection(connection);
		}

	}

	@Override
	public Contact findById(int id) throws DAOException {
		try {
			connection = Manager.getConnection();
			String sql = "SELECT * FROM contact WHERE id = '" + id + "'";
			ps = connection.prepareStatement(sql);
			rs = ps.executeQuery();
			int count = 0;
			while (rs.next()) {
				count++;
				if (count == 1) {
					contact.setFirst_name(rs.getString(1));
					contact.setFirst_name(rs.getString(2));
					contact.setSecond_name(rs.getString(3));
					contact.setThird_name(rs.getString(4));
					contact.setLast_name(rs.getString(5));
					contact.setAddress(rs.getString(6));
					contact.setCity_id(rs.getInt(7));
					contact.setLand_phone(rs.getString(8));
					contact.setMobile_number(rs.getString(9));
					contact.setEmail(rs.getString(10));
					contact.setWebsite(rs.getString(11));
					contact.setJob(rs.getString(12));
					contact.setWork_id(rs.getInt(13));
					contact.setOnline_account_id(rs.getInt(14));
					contact.setUsername_id(rs.getInt(15));
				}
			}
		} catch (SQLException s) {
			s.printStackTrace();
		}
		return contact;
	}

	@Override
	public List<Contact> findAll() throws DAOException {
		try {
			connection = Manager.getConnection();
			String sql = "SELECT * FROM contact ";
			ps = connection.prepareStatement(sql);
			rs = ps.executeQuery();
			int count = 0;
			while (rs.next()) {
				count++;
				if (count == 1) {
					contact.setFirst_name(rs.getString(1));
					contact.setFirst_name(rs.getString(2));
					contact.setSecond_name(rs.getString(3));
					contact.setThird_name(rs.getString(4));
					contact.setLast_name(rs.getString(5));
					contact.setAddress(rs.getString(6));
					contact.setCity_id(rs.getInt(7));
					contact.setLand_phone(rs.getString(8));
					contact.setMobile_number(rs.getString(9));
					contact.setEmail(rs.getString(10));
					contact.setWebsite(rs.getString(11));
					contact.setJob(rs.getString(12));
					contact.setWork_id(rs.getInt(13));
					contact.setOnline_account_id(rs.getInt(14));
					contact.setUsername_id(rs.getInt(15));

					contactList.add(contact);
				}
			}
		} catch (SQLException s) {
			s.printStackTrace();
		}
		return contactList;
	}

	@Override
	public void delete(int id) throws DAOException {
		try {
			connection = Manager.getConnection();
			String sql = " DELETE FROM contact WHERE id = '" + id + "'";
			PreparedStatement ps = connection.prepareStatement(sql);
			int result = ps.executeUpdate();
			if (result != 1) {
				ps.setString(1, contact.getFirst_name());
				ps.setString(2, contact.getSecond_name());
				ps.setString(3, contact.getThird_name());
				ps.setString(4, contact.getLast_name());
				ps.setString(5, contact.getAddress());
				ps.setInt(6, contact.getCity_id());
				ps.setString(7, contact.getLand_phone());
				ps.setString(8, contact.getMobile_number());
				ps.setString(9, contact.getEmail());
				ps.setString(10, contact.getWebsite());
				ps.setString(11, contact.getJob());
				ps.setInt(12, contact.getWork_id());
				ps.setInt(13, contact.getOnline_account_id());
				ps.setInt(14, contact.getUsername_id());
			}

		} catch (SQLException s) {
			s.printStackTrace();

		}

	}

	@Override
	public void update(Contact o) throws DAOException {
		try {
			connection = Manager.getConnection();
			String sql = "UPDATE contact SET first_name'" + o.getFirst_name()
					+ "', second_name = '" + o.getSecond_name()
					+ "', third_name ='" + o.getThird_name()
					+ "', last_name ='" + o.getLast_name() + "', address ='"
					+ o.getAddress() + "', city_id ='" + o.getCity_id()
					+ "', land_phone ='" + o.getLand_phone()
					+ "', mobile_number ='" + o.getMobile_number()
					+ "', email ='" + o.getEmail() + "', website ='"
					+ o.getWebsite() + "', job ='" + o.getJob()
					+ "', work_id = '" + o.getWork_id()
					+ "', online_account_id = '" + o.getOnline_account_id()
					+ "', username_id ='" + o.getUsername_id()
					+ "' WHERE id = " + o.getId();
			ps = connection.prepareStatement(sql);
			int result = ps.executeUpdate(sql);

			if (result != 1) {
				ps.setString(1, contact.getFirst_name());
				ps.setString(2, contact.getSecond_name());
				ps.setString(3, contact.getThird_name());
				ps.setString(4, contact.getLast_name());
				ps.setString(5, contact.getAddress());
				ps.setInt(6, contact.getCity_id());
				ps.setString(7, contact.getLand_phone());
				ps.setString(8, contact.getMobile_number());
				ps.setString(9, contact.getEmail());
				ps.setString(10, contact.getWebsite());
				ps.setString(11, contact.getJob());
				ps.setInt(12, contact.getWork_id());
				ps.setInt(13, contact.getOnline_account_id());
				ps.setInt(14, contact.getUsername_id());
			}

		} catch (SQLException s) {
			s.printStackTrace();
		}

	}

	@Override
	public Contact findByFirst_name(String first_name) throws DAOException {
		try {
			connection = Manager.getConnection();
			String sql = "SELECT * FROM contact WHERE first_name = '"
					+ first_name + "'";
			ps = connection.prepareStatement(sql);
			rs = ps.executeQuery();
			int count = 0;
			while (rs.next()) {
				count++;
				if (count == 1) {
					contact.setFirst_name(rs.getString(1));
					contact.setFirst_name(rs.getString(2));
					contact.setSecond_name(rs.getString(3));
					contact.setThird_name(rs.getString(4));
					contact.setLast_name(rs.getString(5));
					contact.setAddress(rs.getString(6));
					contact.setCity_id(rs.getInt(7));
					contact.setLand_phone(rs.getString(8));
					contact.setMobile_number(rs.getString(9));
					contact.setEmail(rs.getString(10));
					contact.setWebsite(rs.getString(11));
					contact.setJob(rs.getString(12));
					contact.setWork_id(rs.getInt(13));
					contact.setOnline_account_id(rs.getInt(14));
					contact.setUsername_id(rs.getInt(15));
				}
			}
		} catch (SQLException s) {
			s.printStackTrace();
		}
		return contact;
	}

	@Override
	public Contact findBySecond_name(String second_name) throws DAOException {
		try {
			connection = Manager.getConnection();
			String sql = "SELECT * FROM contact WHERE second_name = '"
					+ second_name + "'";
			ps = connection.prepareStatement(sql);
			rs = ps.executeQuery();
			int count = 0;
			while (rs.next()) {
				count++;
				if (count == 1) {
					contact.setFirst_name(rs.getString(1));
					contact.setFirst_name(rs.getString(2));
					contact.setSecond_name(rs.getString(3));
					contact.setThird_name(rs.getString(4));
					contact.setLast_name(rs.getString(5));
					contact.setAddress(rs.getString(6));
					contact.setCity_id(rs.getInt(7));
					contact.setLand_phone(rs.getString(8));
					contact.setMobile_number(rs.getString(9));
					contact.setEmail(rs.getString(10));
					contact.setWebsite(rs.getString(11));
					contact.setJob(rs.getString(12));
					contact.setWork_id(rs.getInt(13));
					contact.setOnline_account_id(rs.getInt(14));
					contact.setUsername_id(rs.getInt(15));
				}
			}
		} catch (SQLException s) {
			s.printStackTrace();
		}
		return contact;
	}

	@Override
	public Contact findByThird_name(String third_name) throws DAOException {
		try {
			connection = Manager.getConnection();
			String sql = "SELECT * FROM contact WHERE third_name = '"
					+ third_name + "'";
			ps = connection.prepareStatement(sql);
			rs = ps.executeQuery();
			int count = 0;
			while (rs.next()) {
				count++;
				if (count == 1) {
					contact.setFirst_name(rs.getString(1));
					contact.setFirst_name(rs.getString(2));
					contact.setSecond_name(rs.getString(3));
					contact.setThird_name(rs.getString(4));
					contact.setLast_name(rs.getString(5));
					contact.setAddress(rs.getString(6));
					contact.setCity_id(rs.getInt(7));
					contact.setLand_phone(rs.getString(8));
					contact.setMobile_number(rs.getString(9));
					contact.setEmail(rs.getString(10));
					contact.setWebsite(rs.getString(11));
					contact.setJob(rs.getString(12));
					contact.setWork_id(rs.getInt(13));
					contact.setOnline_account_id(rs.getInt(14));
					contact.setUsername_id(rs.getInt(15));
				}
			}
		} catch (SQLException s) {
			s.printStackTrace();
		}
		return contact;
	}

	@Override
	public Contact findByLast_name(String last_name) throws DAOException {
		try {
			connection = Manager.getConnection();
			String sql = "SELECT * FROM contact WHERE last_name = '" + last_name
					+ "'";
			ps = connection.prepareStatement(sql);
			rs = ps.executeQuery();
			int count = 0;
			while (rs.next()) {
				count++;
				if (count == 1) {
					contact.setFirst_name(rs.getString(1));
					contact.setFirst_name(rs.getString(2));
					contact.setSecond_name(rs.getString(3));
					contact.setThird_name(rs.getString(4));
					contact.setLast_name(rs.getString(5));
					contact.setAddress(rs.getString(6));
					contact.setCity_id(rs.getInt(7));
					contact.setLand_phone(rs.getString(8));
					contact.setMobile_number(rs.getString(9));
					contact.setEmail(rs.getString(10));
					contact.setWebsite(rs.getString(11));
					contact.setJob(rs.getString(12));
					contact.setWork_id(rs.getInt(13));
					contact.setOnline_account_id(rs.getInt(14));
					contact.setUsername_id(rs.getInt(15));
				}
			}
		} catch (SQLException s) {
			s.printStackTrace();
		}
		return contact;
	}

	@Override
	public Contact findByAddress(String address) throws DAOException {
		try {
			connection = Manager.getConnection();
			String sql = "SELECT * FROM contact WHERE address = '" + address
					+ "'";
			ps = connection.prepareStatement(sql);
			rs = ps.executeQuery();
			int count = 0;
			while (rs.next()) {
				count++;
				if (count == 1) {
					contact.setFirst_name(rs.getString(1));
					contact.setFirst_name(rs.getString(2));
					contact.setSecond_name(rs.getString(3));
					contact.setThird_name(rs.getString(4));
					contact.setLast_name(rs.getString(5));
					contact.setAddress(rs.getString(6));
					contact.setCity_id(rs.getInt(7));
					contact.setLand_phone(rs.getString(8));
					contact.setMobile_number(rs.getString(9));
					contact.setEmail(rs.getString(10));
					contact.setWebsite(rs.getString(11));
					contact.setJob(rs.getString(12));
					contact.setWork_id(rs.getInt(13));
					contact.setOnline_account_id(rs.getInt(14));
					contact.setUsername_id(rs.getInt(15));
				}
			}
		} catch (SQLException s) {
			s.printStackTrace();
		}
		return contact;
	}

	@Override
	public List<Contact> findByCity_id(int city_id) throws DAOException {
		try {
			connection = Manager.getConnection();
			String sql = "SELECT * FROM contact WHERE city_id = '" + city_id
					+ "'";
			ps = connection.prepareStatement(sql);
			rs = ps.executeQuery();
			int count = 0;
			while (rs.next()) {
				count++;
				if (count == 1) {
					contact.setFirst_name(rs.getString(1));
					contact.setFirst_name(rs.getString(2));
					contact.setSecond_name(rs.getString(3));
					contact.setThird_name(rs.getString(4));
					contact.setLast_name(rs.getString(5));
					contact.setAddress(rs.getString(6));
					contact.setCity_id(rs.getInt(7));
					contact.setLand_phone(rs.getString(8));
					contact.setMobile_number(rs.getString(9));
					contact.setEmail(rs.getString(10));
					contact.setWebsite(rs.getString(11));
					contact.setJob(rs.getString(12));
					contact.setWork_id(rs.getInt(13));
					contact.setOnline_account_id(rs.getInt(14));
					contact.setUsername_id(rs.getInt(15));
					contactList.add(contact);
				}
			}
		} catch (SQLException s) {
			s.printStackTrace();
		}
		return contactList;
	}

	@Override
	public Contact findByLand_phone(String land_phone) throws DAOException {
		try {
			connection = Manager.getConnection();
			String sql = "SELECT * FROM contact WHERE land_phone = '"
					+ land_phone + "'";
			ps = connection.prepareStatement(sql);
			rs = ps.executeQuery();
			int count = 0;
			while (rs.next()) {
				count++;
				if (count == 1) {
					contact.setFirst_name(rs.getString(1));
					contact.setFirst_name(rs.getString(2));
					contact.setSecond_name(rs.getString(3));
					contact.setThird_name(rs.getString(4));
					contact.setLast_name(rs.getString(5));
					contact.setAddress(rs.getString(6));
					contact.setCity_id(rs.getInt(7));
					contact.setLand_phone(rs.getString(8));
					contact.setMobile_number(rs.getString(9));
					contact.setEmail(rs.getString(10));
					contact.setWebsite(rs.getString(11));
					contact.setJob(rs.getString(12));
					contact.setWork_id(rs.getInt(13));
					contact.setOnline_account_id(rs.getInt(14));
					contact.setUsername_id(rs.getInt(15));
				}
			}
		} catch (SQLException s) {
			s.printStackTrace();
		}
		return contact;
	}

	@Override
	public Contact findByMobile_number(String mobile_number)
			throws DAOException {
		try {
			connection = Manager.getConnection();
			String sql = "SELECT * FROM contact WHERE mobile_number = '"
					+ mobile_number + "'";
			ps = connection.prepareStatement(sql);
			rs = ps.executeQuery();
			int count = 0;
			while (rs.next()) {
				count++;
				if (count == 1) {
					contact.setFirst_name(rs.getString(1));
					contact.setFirst_name(rs.getString(2));
					contact.setSecond_name(rs.getString(3));
					contact.setThird_name(rs.getString(4));
					contact.setLast_name(rs.getString(5));
					contact.setAddress(rs.getString(6));
					contact.setCity_id(rs.getInt(7));
					contact.setLand_phone(rs.getString(8));
					contact.setMobile_number(rs.getString(9));
					contact.setEmail(rs.getString(10));
					contact.setWebsite(rs.getString(11));
					contact.setJob(rs.getString(12));
					contact.setWork_id(rs.getInt(13));
					contact.setOnline_account_id(rs.getInt(14));
					contact.setUsername_id(rs.getInt(15));
				}
			}
		} catch (SQLException s) {
			s.printStackTrace();
		}
		return contact;
	}

	@Override
	public Contact findByEmail(String email) throws DAOException {
		try {
			connection = Manager.getConnection();
			String sql = "SELECT * FROM contact WHERE email = '" + email + "'";
			ps = connection.prepareStatement(sql);
			rs = ps.executeQuery();
			int count = 0;
			while (rs.next()) {
				count++;
				if (count == 1) {
					contact.setFirst_name(rs.getString(1));
					contact.setFirst_name(rs.getString(2));
					contact.setSecond_name(rs.getString(3));
					contact.setThird_name(rs.getString(4));
					contact.setLast_name(rs.getString(5));
					contact.setAddress(rs.getString(6));
					contact.setCity_id(rs.getInt(7));
					contact.setLand_phone(rs.getString(8));
					contact.setMobile_number(rs.getString(9));
					contact.setEmail(rs.getString(10));
					contact.setWebsite(rs.getString(11));
					contact.setJob(rs.getString(12));
					contact.setWork_id(rs.getInt(13));
					contact.setOnline_account_id(rs.getInt(14));
					contact.setUsername_id(rs.getInt(15));
				}
			}
		} catch (SQLException s) {
			s.printStackTrace();
		}
		return contact;

	}

	@Override
	public Contact findByWebsite(String website) throws DAOException {
		try {
			connection = Manager.getConnection();
			String sql = "SELECT * FROM contact WHERE website = '" + website
					+ "'";
			ps = connection.prepareStatement(sql);
			rs = ps.executeQuery();
			int count = 0;
			while (rs.next()) {
				count++;
				if (count == 1) {
					contact.setFirst_name(rs.getString(1));
					contact.setFirst_name(rs.getString(2));
					contact.setSecond_name(rs.getString(3));
					contact.setThird_name(rs.getString(4));
					contact.setLast_name(rs.getString(5));
					contact.setAddress(rs.getString(6));
					contact.setCity_id(rs.getInt(7));
					contact.setLand_phone(rs.getString(8));
					contact.setMobile_number(rs.getString(9));
					contact.setEmail(rs.getString(10));
					contact.setWebsite(rs.getString(11));
					contact.setJob(rs.getString(12));
					contact.setWork_id(rs.getInt(13));
					contact.setOnline_account_id(rs.getInt(14));
					contact.setUsername_id(rs.getInt(15));
				}
			}
		} catch (SQLException s) {
			s.printStackTrace();
		}
		return contact;
	}

	@Override
	public Contact findByJob(String job) throws DAOException {
		try {
			connection = Manager.getConnection();
			String sql = "SELECT * FROM contact WHERE job = '" + job + "'";
			ps = connection.prepareStatement(sql);
			rs = ps.executeQuery();
			int count = 0;
			while (rs.next()) {
				count++;
				if (count == 1) {
					contact.setFirst_name(rs.getString(1));
					contact.setFirst_name(rs.getString(2));
					contact.setSecond_name(rs.getString(3));
					contact.setThird_name(rs.getString(4));
					contact.setLast_name(rs.getString(5));
					contact.setAddress(rs.getString(6));
					contact.setCity_id(rs.getInt(7));
					contact.setLand_phone(rs.getString(8));
					contact.setMobile_number(rs.getString(9));
					contact.setEmail(rs.getString(10));
					contact.setWebsite(rs.getString(11));
					contact.setJob(rs.getString(12));
					contact.setWork_id(rs.getInt(13));
					contact.setOnline_account_id(rs.getInt(14));
					contact.setUsername_id(rs.getInt(15));
				}
			}
		} catch (SQLException s) {
			s.printStackTrace();
		}
		return contact;
	}

	@Override
	public List<Contact> findByWork_id(int work_id) throws DAOException {
		try {
			connection = Manager.getConnection();
			String sql = "SELECT * FROM contact WHERE work_id = '" + work_id
					+ "'";
			ps = connection.prepareStatement(sql);
			rs = ps.executeQuery();
			int count = 0;
			while (rs.next()) {
				count++;
				if (count == 1) {
					contact.setFirst_name(rs.getString(1));
					contact.setFirst_name(rs.getString(2));
					contact.setSecond_name(rs.getString(3));
					contact.setThird_name(rs.getString(4));
					contact.setLast_name(rs.getString(5));
					contact.setAddress(rs.getString(6));
					contact.setCity_id(rs.getInt(7));
					contact.setLand_phone(rs.getString(8));
					contact.setMobile_number(rs.getString(9));
					contact.setEmail(rs.getString(10));
					contact.setWebsite(rs.getString(11));
					contact.setJob(rs.getString(12));
					contact.setWork_id(rs.getInt(13));
					contact.setOnline_account_id(rs.getInt(14));
					contact.setUsername_id(rs.getInt(15));
					contactList.add(contact);
				}
			}
		} catch (SQLException s) {
			s.printStackTrace();
		}
		return contactList;
	}

	@Override
	public List<Contact> findByOnline_account_id(int online_account_id)
			throws DAOException {
		try {
			connection = Manager.getConnection();
			String sql = "SELECT * FROM contact WHERE online_account_id = '"
					+ online_account_id + "'";
			ps = connection.prepareStatement(sql);
			rs = ps.executeQuery();
			int count = 0;
			while (rs.next()) {
				count++;
				if (count == 1) {
					contact.setFirst_name(rs.getString(1));
					contact.setFirst_name(rs.getString(2));
					contact.setSecond_name(rs.getString(3));
					contact.setThird_name(rs.getString(4));
					contact.setLast_name(rs.getString(5));
					contact.setAddress(rs.getString(6));
					contact.setCity_id(rs.getInt(7));
					contact.setLand_phone(rs.getString(8));
					contact.setMobile_number(rs.getString(9));
					contact.setEmail(rs.getString(10));
					contact.setWebsite(rs.getString(11));
					contact.setJob(rs.getString(12));
					contact.setWork_id(rs.getInt(13));
					contact.setOnline_account_id(rs.getInt(14));
					contact.setUsername_id(rs.getInt(15));
					contactList.add(contact);
				}
			}
		} catch (SQLException s) {
			s.printStackTrace();
		}
		return contactList;
	}

	@Override
	public List<Contact> findByUsername_id(int username_id) throws DAOException {
		try {
			connection = Manager.getConnection();
			String sql = "SELECT * FROM contact WHERE username_id = '"
					+ username_id + "'";
			ps = connection.prepareStatement(sql);
			rs = ps.executeQuery();
			int count = 0;
			while (rs.next()) {
				count++;
				if (count == 1) {
					contact.setFirst_name(rs.getString(1));
					contact.setFirst_name(rs.getString(2));
					contact.setSecond_name(rs.getString(3));
					contact.setThird_name(rs.getString(4));
					contact.setLast_name(rs.getString(5));
					contact.setAddress(rs.getString(6));
					contact.setCity_id(rs.getInt(7));
					contact.setLand_phone(rs.getString(8));
					contact.setMobile_number(rs.getString(9));
					contact.setEmail(rs.getString(10));
					contact.setWebsite(rs.getString(11));
					contact.setJob(rs.getString(12));
					contact.setWork_id(rs.getInt(13));
					contact.setOnline_account_id(rs.getInt(14));
					contact.setUsername_id(rs.getInt(15));
					contactList.add(contact);
				}
			}
		} catch (SQLException s) {
			s.printStackTrace();
		}
		return contactList;
	}

}
